# coding:utf-8
import re
import sys

from PyQt5.QtCore import Qt
from PyQt5.QtGui import QStandardItemModel, QStandardItem
from PyQt5.QtWidgets import QApplication, QMainWindow, QTableView, QFileDialog, QMessageBox
from openpyxl import load_workbook, Workbook

import form


def find(cell, pattern='[1-9]\d{5}(18|19|20|(3\d))\d{2}((0[1-9])|(1[0-2]))(([0-2][1-9])|10|20|30|31)\d{3}[0-9Xx]'):
    p = re.compile(pattern)
    ans = p.search(cell)
    if ans:
        return str(ans.group(0))
    return '匹配不到'


class MainDialog(QMainWindow):
    def __init__(self, parent=None):
        super(QMainWindow, self).__init__(parent)
        self.table_model = None  # 表格模型
        self.ui = form.Ui_MainWindow()
        self.ui.setupUi(self)

        self.ui.action_Excel.triggered.connect(self.openFile)
        self.ui.pushButton.clicked.connect(self.search)
        self.ui.action_Excel_2.triggered.connect(self.export_excel)

        # 初始化excel和加载数据
        self.excel_data = []

    def load_data_to_table(self, data):
        # 创建表格模型
        self.table_model = QStandardItemModel(len(data), len(data[0]))

        # 加载数据到表格模型
        for row in range(len(data)):
            for col in range(len(data[row])):
                item = QStandardItem(str(data[row][col]))
                self.table_model.setItem(row, col, item)

        # 设置tableView的模型
        self.ui.tableView.setModel(self.table_model)
        self.ui.tableView.resizeColumnsToContents()
        self.ui.tableView.horizontalHeader()  # setStretchLastSection(True)
        self.ui.tableView.setEditTriggers(QTableView.NoEditTriggers)  # 设置为不可编辑
        self.ui.tableView.setSelectionBehavior(QTableView.SelectRows)
        self.ui.tableView.setSelectionMode(QTableView.SingleSelection)
        self.ui.tableView.setFocusPolicy(Qt.NoFocus)  # 设置没有选中的虚线框

    def openFile(self):
        options = QFileDialog.Options()
        options |= QFileDialog.ReadOnly
        file_path, _ = QFileDialog.getOpenFileName(self, "选择Excel文件", "", "Excel Files (*.xlsx *.xls)",
                                                   options=options)
        if file_path is None:
            return

        # 清空数据
        self.excel_data.clear()

        wb = load_workbook(file_path)
        sheet = wb.active

        for row in sheet.iter_rows(values_only=True):
            self.excel_data.append(row)

        # 关闭Excel文件
        wb.close()

        self.load_data_to_table(self.excel_data)

    def search(self):

        temp_data = []

        text = self.ui.lineEdit.text()
        if not self.excel_data:
            QMessageBox.information(self, "提示", "请打开文件！")
            return

        for row in self.excel_data:
            row_temp = []
            for cell in row:
                row_temp.append(find(str(cell), text))
            temp_data.append(row_temp)

        self.load_data_to_table(temp_data)

    def export_excel(self):
        # 弹出文件选择对话框，获取保存的文件路径
        file_path, _ = QFileDialog.getSaveFileName(self, "Save File", "", "Excel Files (*.xlsx)")
        if file_path:
            # 创建一个Workbook对象
            workbook = Workbook()
            worksheet = workbook.active

            # 获取表格的行数和列数
            rows = self.table_model.rowCount()
            cols = self.table_model.columnCount()

            # 遍历表格的每个单元格，并将数据写入Excel文件中
            for row in range(rows):
                for col in range(cols):
                    item = self.table_model.item(row, col)
                    if item is not None:
                        value = item.text()
                        worksheet.cell(row=row + 1, column=col + 1, value=value)

            # 保存Excel文件
            workbook.save(file_path)
            QMessageBox.information(self, "提示", "导出成功！")


if __name__ == '__main__':
    myapp = QApplication(sys.argv)
    myDlg = MainDialog()
    myDlg.show()
    sys.exit(myapp.exec_())
